package Datebase;

import javax.swing.table.DefaultTableCellRenderer;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.net.URL;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.border.EmptyBorder;
import javax.swing.GroupLayout.Alignment;
import javax.swing.text.StyledDocument;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class tdepartment extends JFrame {

    public static void main(String[] args) {
        System.setProperty("sun.java2d.noddraw", "true");
        new tdepartment();
    }
    public tdepartment(){
        JFrame department = new JFrame("院系查询界面");
        URL resource = tdepartment.class.getClassLoader().getResource("kun.png");
        Image image = new ImageIcon(resource).getImage();
        department.setIconImage(image);
        department.setSize(800, 600);
        department.setLocationRelativeTo(null);
        department.setLayout(null);

        JLabel sele = new JLabel("请输入院系名称:");

        sele.setForeground(new Color(0x0A0A0A));
        sele.setFont(new Font("黑体", Font.PLAIN, 15));
        sele.setBounds(200, 20, 200, 100);
        department.add(sele);
//添加用户名输入
        JTextField use = new JTextField(20);
        use.setFont(new Font("黑体", Font.PLAIN, 18));
        use.setSelectedTextColor(new Color(0x0A0A0A));
        use.setBounds(330, 50, 200, 40);
        department.add(use);

        JButton button6 = new JButton("查询");
        button6.setFont(new Font("黑体", Font.PLAIN, 15));

        button6.setBounds(580, 50, 100, 40);
        department.add(button6);

        button6.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {

                try {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    String dbURL = "jdbc:oracle:thin:@localhost:1521:XE";
                    String user = "HR";
                    String password = "root";
                    Connection conn = DriverManager.getConnection(dbURL, user, password);
                    String sql = "SELECT MAX(GRADE),MIN(GRADE),CNAME,DEPARTMENT,ROUND(AVG(grade),2)\n" +
                            "FROM EXAM,COURSE,STUDENT\n" +
                            "WHERE COURSE.CNO=EXAM.CNO and EXAM.CNO=COURSE.CNO AND STUDENT.SNO=EXAM.SNO AND DEPARTMENT='" + use.getText() + "'" +
                            "GROUP BY CNAME,DEPARTMENT\n";

                    PreparedStatement pstm = conn.prepareStatement(sql);
                    ResultSet rs = pstm.executeQuery();

                    int count = 0;
                    while (rs.next()) {
                        count++;
                    }
                    if(count == 0)
                    {
                        JOptionPane.showMessageDialog(null, "输入的院系有误!",
                                "提示", JOptionPane.WARNING_MESSAGE);
                    }
                    else {
                        rs = pstm.executeQuery();

                        Object[][] data = new Object[count][5];
                        String[] title = {"课程名", "系部", "平均成绩", "最高成绩", "最低成绩"};
                        count = 0;
                        while (rs.next()) {
                            data[count][0] = (rs.getString("CNAME"));
                            data[count][1] = (rs.getString("DEPARTMENT"));
                            data[count][2] = (rs.getString("ROUND(AVG(grade),2)"));
                            data[count][3] = (rs.getString("MAX(GRADE)"));
                            data[count][4] = (rs.getString("MIN(GRADE)"));
                            count++;
                        }
                        JTable table = new JTable(data, title);
                        JScrollPane scrollPane = new JScrollPane(table);
                        DefaultTableCellRenderer cr = new DefaultTableCellRenderer();
                        cr.setHorizontalAlignment(SwingConstants.CENTER);
                        table.setDefaultRenderer(Object.class, cr);

                        table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
                        table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
                        table.getColumnModel().getColumn(0).setPreferredWidth(200);
                        table.getColumnModel().getColumn(1).setPreferredWidth(200);
                        table.getColumnModel().getColumn(2).setPreferredWidth(200);
                        table.getColumnModel().getColumn(3).setPreferredWidth(200);
                        table.getColumnModel().getColumn(4).setPreferredWidth(200);
                        table.setRowHeight(80);
                        table.setFont(new Font("微软雅黑", Font.BOLD, 13));
                        scrollPane.setBounds(60, 110, 700, 260);
                        department.add(scrollPane);



                        Class.forName("oracle.jdbc.driver.OracleDriver");
                        String dbURL1 = "jdbc:oracle:thin:@localhost:1521:XE";
                        String user1 = "HR";
                        String password1 = "root";
                        Connection conn1 = DriverManager.getConnection(dbURL1, user1, password1);
                        String sql1 = "SELECT CNAME,DEPARTMENT,COUNT(*)\n" +
                                "FROM EXAM,COURSE,STUDENT\n" +
                                "WHERE COURSE.CNO=EXAM.CNO and EXAM.CNO=COURSE.CNO AND STUDENT.SNO=EXAM.SNO and GRADE<60 and DEPARTMENT='" + use.getText() + "'" +
                                "GROUP BY CNAME,DEPARTMENT\n";
                        PreparedStatement pstm1 = conn1.prepareStatement(sql1);
                        ResultSet rs1 = pstm1.executeQuery();

                        int count1 = 0;
                        while (rs1.next()) {
                            count1++;
                        }
                        rs1 = pstm1.executeQuery();

                        Object[][] data1 = new Object[count1][3];
                        String[] title1 = {"课程名", "系部", "不及格人数"};
                        count1 = 0;
                        while (rs1.next()) {
                            data1[count1][0] = (rs1.getString("CNAME"));
                            data1[count1][1] = (rs1.getString("DEPARTMENT"));
                            data1[count1][2] = (rs1.getString("COUNT(*)"));
                            count1++;
                        }
                        JTable table1 = new JTable(data1, title1);
                        JScrollPane scrollPane1 = new JScrollPane(table1);
                        DefaultTableCellRenderer cr1 = new DefaultTableCellRenderer();
                        cr1.setHorizontalAlignment(SwingConstants.CENTER);
                        table1.setDefaultRenderer(Object.class, cr1);

                        table1.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
                        table1.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
                        table1.getColumnModel().getColumn(0).setPreferredWidth(200);
                        table1.getColumnModel().getColumn(1).setPreferredWidth(200);
                        table1.getColumnModel().getColumn(2).setPreferredWidth(200);
                        table1.setRowHeight(80);
                        table1.setFont(new Font("微软雅黑", Font.BOLD, 13));
                        scrollPane1.setBounds(60, 380, 400, 180);
                        department.add(scrollPane1);

                        JButton buttonn = new JButton("打印成绩");
                        buttonn.setFont(new Font("黑体", Font.PLAIN, 15));

                        buttonn.setBounds(580, 500, 100, 40);
                        department.repaint();
                        department.add(buttonn);
                        buttonn.addActionListener(new ActionListener() {
                            @Override
                            public void actionPerformed(ActionEvent e) {
                                if(e.getSource() == buttonn) {
                                    try {
                                        Class.forName("oracle.jdbc.driver.OracleDriver");
                                        String dbURL = "jdbc:oracle:thin:@localhost:1521:XE";
                                        String user = "HR";
                                        String password = "root";
                                        Connection connection = DriverManager.getConnection(dbURL, user, password);

                                        Statement statement = connection.createStatement();
                                        ResultSet resultSet = statement.executeQuery("SELECT MAX(GRADE),MIN(GRADE),CNAME,DEPARTMENT,ROUND(AVG(grade),2)\n" +
                                                "FROM EXAM,COURSE,STUDENT\n" +
                                                "WHERE COURSE.CNO=EXAM.CNO and EXAM.CNO=COURSE.CNO AND STUDENT.SNO=EXAM.SNO AND DEPARTMENT='" + use.getText() + "'" +
                                                "GROUP BY CNAME,DEPARTMENT\n");

                                        FileWriter csvWriter = new FileWriter("D:\\桌面\\院系成绩.csv");
                                        int columnCount = resultSet.getMetaData().getColumnCount();
                                        for (int i = 1; i <= columnCount; i++) {
                                            csvWriter.append(resultSet.getMetaData().getColumnName(i));
                                            if (i < columnCount) {
                                                csvWriter.append(",");
                                            }
                                        }
                                        csvWriter.append("\n");
                                        while (resultSet.next()) {
                                            for (int i = 1; i <= columnCount; i++) {
                                                csvWriter.append(resultSet.getString(i));
                                                if (i < columnCount) {
                                                    csvWriter.append(",");
                                                }
                                            }
                                            csvWriter.append("\n");
                                        }
                                        csvWriter.flush();
                                        csvWriter.close();
                                        System.out.println("打印成功！");
                                    } catch (SQLException | IOException e1) {
                                        e1.printStackTrace();
                                    }catch (ClassNotFoundException cnfe) {
                                        cnfe.printStackTrace();
                                        JOptionPane.showMessageDialog(null, "数据源错误", "错误", JOptionPane.ERROR_MESSAGE);
                                    }

                                }
                            }
                        });

                    }

                } catch (ClassNotFoundException cnfe) {
                    cnfe.printStackTrace();
                    JOptionPane.showMessageDialog(null, "数据源错误", "错误", JOptionPane.ERROR_MESSAGE);
                } catch (SQLException sqle) {
                    sqle.printStackTrace();
                    JOptionPane.showMessageDialog(null, "数据操作错误", "错误", JOptionPane.ERROR_MESSAGE);
                }


            }
        });


        department.repaint();
        department.setVisible(true);

    }

}
